package cn.edu.nju.testdb.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Random;

import cn.edu.nju.testdb.dao.BaseDao;
import cn.edu.nju.testdb.dao.TestDao;

public class TestDaoImpl extends BaseDao implements TestDao {

	private static final String[] nameList = {
		"Mike","Bob","Jack","Alice","Cathy","Ann","Betty","Cindy","Mary","Jane",
	};
	private static Random random = new Random(1017);
	
	@Override
	public boolean randomInsert(int quantitiy) {
		System.out.println("TestDaoImpl.randomInsert() begin"); 
		boolean success = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			pstmt = conn
					.prepareStatement("insert into test(col1,col2,col3) values (?,?,?)");

			for (int i = 1; i <= quantitiy; i++) {
				pstmt.setString(1, nameList[random.nextInt(nameList.length)]);
				pstmt.setString(2, getCol2());
				pstmt.setInt(3, random.nextInt(20)+1);//1 to 20
				pstmt.addBatch();
				if (i % 1000 == 0) {// 批处理1000条
					pstmt.executeBatch();
					conn.commit();
					pstmt.clearBatch();
				}
			}
			pstmt.executeBatch();
			conn.commit();
			pstmt.clearBatch();
			
			success = true;

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closeAll(conn, pstmt,null);
		}
		return success;
	}
	
	public boolean sp_RandomInsert(int quantitiy) {
		System.out.println("TestDaoImpl.randomInsert() begin"); 
		boolean success = false;
		Connection conn = null;
		CallableStatement cstmt = null;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			cstmt = conn.prepareCall("{call dbo.sp_RandomInsert(?,?,?)}");

			for (int i = 1; i <= quantitiy; i++) {
				cstmt.setString(1, nameList[random.nextInt(nameList.length)]);
				cstmt.setString(2, getCol2());
				cstmt.setInt(3, random.nextInt(20)+1);//1 to 20
				cstmt.addBatch();
				if (i % 1000 == 0) {// 批处理1000条
					cstmt.executeBatch();
					conn.commit();
					cstmt.clearBatch();
				}
			}
			cstmt.executeBatch();
			conn.commit();
			cstmt.clearBatch();
			
			success = true;

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				cstmt.close();
				conn.close();
			} catch (Exception e2) {
			}

		}
		return success;
	}
	
	/**
	 * 获得abcde五个字母随机出现的长度为5的字符串，比如aacdc
	 * @return
	 */
	public String getCol2() {
		String col2 = "";
		for (int i = 0; i < 5; i++) {
			col2 += (char)('a'+random.nextInt(5));
		}
		return col2;
	}
	
	public void executeCountByCol1() {
		System.out.println("TestDaoImpl.executeCountByCol1() begin");
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConn();
			pstmt = conn
					.prepareStatement("select count(*) from test group by col1 order by count(*);");
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closeAll(conn, pstmt,null);
		}
	}
	
	public void executeFindByLike() {
		System.out.println("TestDaoImpl.executeFindByLike() begin");
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConn();
/*			pstmt = conn
					.prepareStatement("select * from test where col2 like 'ab%';");
*/			pstmt = conn
				.prepareStatement("select * from test where col2<='abeee' and col2>'ab';");
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closeAll(conn, pstmt,null);
		}
	}
}
